// 数据库初始化脚本
require('dotenv').config()
const mysql = require('mysql2/promise')
const bcrypt = require('bcryptjs')

async function initDatabase() {
  console.log('\n========================================')
  console.log('🚀 开始初始化数据库...')
  console.log('========================================\n')

  try {
    // 连接数据库
    const connection = await mysql.createConnection({
      host: process.env.DB_HOST,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      database: process.env.DB_NAME,
      multipleStatements: true
    })

    console.log('✅ 数据库连接成功')

    // 1. 创建用户表
    console.log('\n📋 创建用户表...')
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS jd_users (
        id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
        username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
        password VARCHAR(255) NOT NULL COMMENT '密码（加密）',
        nickname VARCHAR(100) DEFAULT NULL COMMENT '昵称',
        email VARCHAR(100) DEFAULT NULL COMMENT '邮箱',
        phone VARCHAR(20) DEFAULT NULL COMMENT '手机号',
        avatar VARCHAR(255) DEFAULT NULL COMMENT '头像URL',
        gender TINYINT DEFAULT 0 COMMENT '性别：0-未知，1-男，2-女',
        birthday DATE DEFAULT NULL COMMENT '生日',
        is_admin TINYINT DEFAULT 0 COMMENT '是否管理员：0-否，1-是',
        status TINYINT DEFAULT 1 COMMENT '状态：0-禁用，1-正常',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        INDEX idx_username (username),
        INDEX idx_phone (phone),
        INDEX idx_email (email)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表'
    `)
    console.log('✅ 用户表创建成功')

    // 2. 创建商品分类表
    console.log('\n📋 创建商品分类表...')
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS jd_categories (
        id INT PRIMARY KEY AUTO_INCREMENT COMMENT '分类ID',
        name VARCHAR(50) NOT NULL COMMENT '分类名称',
        parent_id INT DEFAULT 0 COMMENT '父分类ID',
        level TINYINT DEFAULT 1 COMMENT '分类层级',
        sort_order INT DEFAULT 0 COMMENT '排序',
        icon VARCHAR(255) DEFAULT NULL COMMENT '图标',
        status TINYINT DEFAULT 1 COMMENT '状态：0-禁用，1-正常',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        INDEX idx_parent_id (parent_id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品分类表'
    `)
    console.log('✅ 商品分类表创建成功')

    // 3. 创建商品表
    console.log('\n📋 创建商品表...')
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS jd_products (
        id INT PRIMARY KEY AUTO_INCREMENT COMMENT '商品ID',
        name VARCHAR(200) NOT NULL COMMENT '商品名称',
        category_id INT NOT NULL COMMENT '分类ID',
        category VARCHAR(50) DEFAULT NULL COMMENT '商品分类名称',
        brand VARCHAR(100) DEFAULT NULL COMMENT '品牌',
        price DECIMAL(10,2) NOT NULL COMMENT '价格',
        original_price DECIMAL(10,2) DEFAULT NULL COMMENT '原价',
        stock INT DEFAULT 0 COMMENT '库存',
        sales INT DEFAULT 0 COMMENT '销量',
        image VARCHAR(255) DEFAULT NULL COMMENT '主图',
        images TEXT DEFAULT NULL COMMENT '商品图片（JSON数组）',
        description TEXT DEFAULT NULL COMMENT '商品描述',
        detail TEXT DEFAULT NULL COMMENT '商品详情',
        specs TEXT DEFAULT NULL COMMENT '商品规格（JSON）',
        status TINYINT DEFAULT 1 COMMENT '状态：0-下架，1-上架',
        is_hot TINYINT DEFAULT 0 COMMENT '是否热门',
        is_new TINYINT DEFAULT 0 COMMENT '是否新品',
        is_recommended TINYINT DEFAULT 0 COMMENT '是否推荐',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        INDEX idx_category_id (category_id),
        INDEX idx_category (category),
        INDEX idx_price (price),
        INDEX idx_sales (sales),
        INDEX idx_status (status)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表'
    `)
    console.log('✅ 商品表创建成功')

    // 4. 创建购物车表
    console.log('\n📋 创建购物车表...')
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS carts (
        id INT PRIMARY KEY AUTO_INCREMENT COMMENT '购物车ID',
        user_id INT NOT NULL COMMENT '用户ID',
        product_id INT NOT NULL COMMENT '商品ID',
        quantity INT DEFAULT 1 COMMENT '数量',
        selected TINYINT DEFAULT 1 COMMENT '是否选中：0-否，1-是',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        INDEX idx_user_id (user_id),
        INDEX idx_product_id (product_id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='购物车表'
    `)
    console.log('✅ 购物车表创建成功')

    // 5. 创建收货地址表
    console.log('\n📋 创建收货地址表...')
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS jd_addresses (
        id INT PRIMARY KEY AUTO_INCREMENT COMMENT '地址ID',
        user_id INT NOT NULL COMMENT '用户ID',
        name VARCHAR(50) NOT NULL COMMENT '收货人',
        phone VARCHAR(20) NOT NULL COMMENT '联系电话',
        province VARCHAR(50) NOT NULL COMMENT '省',
        city VARCHAR(50) NOT NULL COMMENT '市',
        district VARCHAR(50) NOT NULL COMMENT '区/县',
        detail VARCHAR(200) NOT NULL COMMENT '详细地址',
        is_default TINYINT DEFAULT 0 COMMENT '是否默认：0-否，1-是',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        INDEX idx_user_id (user_id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='收货地址表'
    `)
    console.log('✅ 收货地址表创建成功')

    // 6. 创建订单表
    console.log('\n📋 创建订单表...')
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS jd_orders (
        id INT PRIMARY KEY AUTO_INCREMENT COMMENT '订单ID',
        order_no VARCHAR(50) NOT NULL UNIQUE COMMENT '订单号',
        user_id INT NOT NULL COMMENT '用户ID',
        total_amount DECIMAL(10,2) NOT NULL COMMENT '订单总额',
        actual_amount DECIMAL(10,2) NOT NULL COMMENT '实付金额',
        status VARCHAR(20) DEFAULT 'pending' COMMENT '订单状态：pending-待付款，paid-待发货，shipped-待收货，completed-已完成，cancelled-已取消',
        payment_method VARCHAR(20) DEFAULT NULL COMMENT '支付方式',
        payment_time TIMESTAMP NULL DEFAULT NULL COMMENT '支付时间',
        ship_time TIMESTAMP NULL DEFAULT NULL COMMENT '发货时间',
        finish_time TIMESTAMP NULL DEFAULT NULL COMMENT '完成时间',
        cancel_time TIMESTAMP NULL DEFAULT NULL COMMENT '取消时间',
        receiver_name VARCHAR(50) NOT NULL COMMENT '收货人',
        receiver_phone VARCHAR(20) NOT NULL COMMENT '收货电话',
        receiver_address VARCHAR(255) NOT NULL COMMENT '收货地址',
        remark VARCHAR(500) DEFAULT NULL COMMENT '订单备注',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        INDEX idx_order_no (order_no),
        INDEX idx_user_id (user_id),
        INDEX idx_status (status)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表'
    `)
    console.log('✅ 订单表创建成功')

    // 7. 创建订单商品表
    console.log('\n📋 创建订单商品表...')
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS jd_order_items (
        id INT PRIMARY KEY AUTO_INCREMENT COMMENT '订单商品ID',
        order_id INT NOT NULL COMMENT '订单ID',
        product_id INT NOT NULL COMMENT '商品ID',
        product_name VARCHAR(200) NOT NULL COMMENT '商品名称',
        product_image VARCHAR(255) DEFAULT NULL COMMENT '商品图片',
        price DECIMAL(10,2) NOT NULL COMMENT '商品单价',
        quantity INT NOT NULL COMMENT '购买数量',
        total_price DECIMAL(10,2) NOT NULL COMMENT '小计',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        INDEX idx_order_id (order_id),
        INDEX idx_product_id (product_id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单商品表'
    `)
    console.log('✅ 订单商品表创建成功')

    // 8. 插入测试数据
    console.log('\n📝 插入测试数据...')

    // 插入管理员账号和测试用户（密码：123456）
    const hashedPassword = await bcrypt.hash('123456', 10)
    
    const [existingAdmin] = await connection.execute(
      'SELECT * FROM jd_users WHERE username = ?',
      ['admin']
    )

    if (existingAdmin.length === 0) {
      await connection.execute(
        'INSERT INTO jd_users (username, password, nickname, email, phone, is_admin) VALUES (?, ?, ?, ?, ?, ?)',
        ['admin', hashedPassword, '管理员', 'admin@example.com', '13800138000', 1]
      )
      console.log('✅ 管理员账号创建成功')
      console.log('   用户名: admin')
      console.log('   密码: 123456')
    } else {
      console.log('ℹ️  管理员账号已存在，跳过创建')
    }

    const [existingTest] = await connection.execute(
      'SELECT * FROM jd_users WHERE username = ?',
      ['test']
    )

    if (existingTest.length === 0) {
      await connection.execute(
        'INSERT INTO jd_users (username, password, nickname, email, phone, is_admin) VALUES (?, ?, ?, ?, ?, ?)',
        ['test', hashedPassword, '测试用户', 'test@example.com', '13800138001', 0]
      )
      console.log('✅ 测试用户创建成功')
      console.log('   用户名: test')
      console.log('   密码: 123456')
    } else {
      console.log('ℹ️  测试用户已存在，跳过创建')
    }

    // 插入商品分类
    const [existingCategories] = await connection.execute('SELECT * FROM jd_categories')
    if (existingCategories.length === 0) {
      await connection.execute(`
        INSERT INTO jd_categories (name, parent_id, level, sort_order) VALUES
        ('数码电器', 0, 1, 1),
        ('服装鞋帽', 0, 1, 2),
        ('食品生鲜', 0, 1, 3),
        ('图书音像', 0, 1, 4),
        ('家居家纺', 0, 1, 5)
      `)
      console.log('✅ 商品分类创建成功')
    } else {
      console.log('ℹ️  商品分类已存在，跳过创建')
    }

    // 插入测试商品
    const [existingProducts] = await connection.execute('SELECT * FROM jd_products')
    if (existingProducts.length === 0) {
      await connection.execute(`
        INSERT INTO jd_products (name, category_id, category, price, original_price, stock, sales, image, description, status) VALUES
        ('iPhone 15 Pro Max', 1, '手机', 9999.00, 11999.00, 100, 0, 'https://img14.360buyimg.com/n1/jfs/t1/210930/20/33929/100801/65331c21F9b9b3a1e/62df73b53e4b2c83.jpg', '最新款苹果手机，性能强劲', 1),
        ('小米14 Ultra', 1, '手机', 5999.00, 6999.00, 200, 0, 'https://img14.360buyimg.com/n1/jfs/t1/210930/20/33929/100801/65331c21F9b9b3a1e/62df73b53e4b2c83.jpg', '高性价比旗舰手机', 1),
        ('华为Mate 60 Pro', 1, '手机', 6999.00, 7999.00, 150, 0, 'https://img14.360buyimg.com/n1/jfs/t1/210930/20/33929/100801/65331c21F9b9b3a1e/62df73b53e4b2c83.jpg', '国产旗舰，性能出色', 1),
        ('MacBook Pro', 1, '电脑', 14999.00, 16999.00, 50, 0, 'https://img14.360buyimg.com/n1/jfs/t1/210930/20/33929/100801/65331c21F9b9b3a1e/62df73b53e4b2c83.jpg', '苹果笔记本电脑，专业首选', 1),
        ('AirPods Pro', 1, '数码', 1999.00, 2299.00, 300, 0, 'https://img14.360buyimg.com/n1/jfs/t1/210930/20/33929/100801/65331c21F9b9b3a1e/62df73b53e4b2c83.jpg', '主动降噪无线耳机', 1)
      `)
      console.log('✅ 测试商品创建成功')
    } else {
      console.log('ℹ️  测试商品已存在，跳过创建')
    }

    // 插入测试订单（使用字符串状态）
    const [existingOrders] = await connection.execute('SELECT * FROM jd_orders')
    if (existingOrders.length === 0) {
      await connection.execute(`
        INSERT INTO jd_orders (order_no, user_id, total_amount, actual_amount, status, receiver_name, receiver_phone, receiver_address, created_at) VALUES
        ('20251101000001', 2, 9999.00, 9999.00, 'pending', '张三', '13800138001', '北京市朝阳区xxx街道xxx号', '2025-11-01 10:00:00'),
        ('20251101000002', 2, 5999.00, 5999.00, 'paid', '张三', '13800138001', '北京市朝阳区xxx街道xxx号', '2025-11-01 11:00:00'),
        ('20251101000003', 2, 6999.00, 6999.00, 'shipped', '张三', '13800138001', '北京市朝阳区xxx街道xxx号', '2025-11-01 12:00:00'),
        ('20251101000004', 2, 14999.00, 14999.00, 'completed', '张三', '13800138001', '北京市朝阳区xxx街道xxx号', '2025-10-31 10:00:00'),
        ('20251101000005', 2, 1999.00, 1999.00, 'cancelled', '张三', '13800138001', '北京市朝阳区xxx街道xxx号', '2025-10-30 10:00:00')
      `)
      console.log('✅ 测试订单创建成功')

      // 插入订单商品明细
      await connection.execute(`
        INSERT INTO jd_order_items (order_id, product_id, product_name, product_image, price, quantity, total_price) VALUES
        (1, 1, 'iPhone 15 Pro Max', 'https://img14.360buyimg.com/n1/jfs/t1/210930/20/33929/100801/65331c21F9b9b3a1e/62df73b53e4b2c83.jpg', 9999.00, 1, 9999.00),
        (2, 2, '小米14 Ultra', 'https://img14.360buyimg.com/n1/jfs/t1/210930/20/33929/100801/65331c21F9b9b3a1e/62df73b53e4b2c83.jpg', 5999.00, 1, 5999.00),
        (3, 3, '华为Mate 60 Pro', 'https://img14.360buyimg.com/n1/jfs/t1/210930/20/33929/100801/65331c21F9b9b3a1e/62df73b53e4b2c83.jpg', 6999.00, 1, 6999.00),
        (4, 4, 'MacBook Pro', 'https://img14.360buyimg.com/n1/jfs/t1/210930/20/33929/100801/65331c21F9b9b3a1e/62df73b53e4b2c83.jpg', 14999.00, 1, 14999.00),
        (5, 5, 'AirPods Pro', 'https://img14.360buyimg.com/n1/jfs/t1/210930/20/33929/100801/65331c21F9b9b3a1e/62df73b53e4b2c83.jpg', 1999.00, 1, 1999.00)
      `)
      console.log('✅ 订单商品明细创建成功')
    } else {
      console.log('ℹ️  测试订单已存在，跳过创建')
    }

    await connection.end()

    console.log('\n========================================')
    console.log('🎉 数据库初始化完成！')
    console.log('========================================\n')
    console.log('📝 测试账号信息：')
    console.log('   管理员 - 用户名: admin, 密码: 123456')
    console.log('   测试用户 - 用户名: test, 密码: 123456')
    console.log('\n📦 已创建 5 个测试订单（包含所有状态）')
    console.log('⚠️  注意：订单状态使用字符串格式（pending/paid/shipped/completed/cancelled）')
    console.log('\n🌐 现在可以访问: http://localhost:5173')
    console.log('\n')

  } catch (error) {
    console.error('❌ 数据库初始化失败：', error.message)
    process.exit(1)
  }
}

// 执行初始化
initDatabase()

